<?
class factura{

	// Propiedades
	var $id;
	var $id_zona;
	var $numero;
	var $fecha;
	var $observaciones;
	var $estatus;
	var $id_persona;
	var $letra_per;
	var $cedula_per;
	var $nombre_per;
	var $direccion_per;
	var $telefono_per;
	var $id_experticia;
    var $tipo_exp;
    var $costo_exp;
    var $fecha_exp;
	var $id_vehiculo;
	var $carroceria_veh;
	var $placa_veh;
	var $marca_veh;
	var $modelo_veh;
	var $color_veh;
	var $anio_veh;
	var $pagos;
	var $km;

	var $monto_pagos;


	var $total;
	
		//PARA LISTADO
	var $conc_tipo_desc;
	var $conc_estatus_desc;

	function get($conn, $id){
		try {
			$q = "SELECT 
					A.id, A.id_zona, lpad(A.numero::text, 10, '0') AS numero, A.fecha, A.observaciones, 
					A.id_experticia, A.estatus, B.id_tipo AS tipo_exp,
					B.costo AS costo_exp, B.fecha AS fecha_exp, A.km, 
					A.id_persona, C.letra AS letra_per, C.cedula AS cedula_per, C.nombre AS nombre_per,
					C.direccion AS direccion_per, C.telefono AS telefono_per,
					B.id_vehiculo, 
					CASE WHEN strpos(D.placa,'S/P')=1 THEN NULL ELSE D.placa END  AS placa_veh, D.carroceria AS carroceria_veh, CASE WHEN D.placa IS NULL THEN 'S' ELSE 'N' END AS sinplaca_veh,
					E.id AS marca_veh, F.id AS modelo_veh, D.id_color AS color_veh, D.anio AS anio_veh
				FROM facturas AS A 
					LEFT JOIN experticias AS B ON A.id_experticia = B.id
					LEFT JOIN personas AS C ON A.id_persona = C.id
					LEFT JOIN vehiculos AS D ON B.id_vehiculo =D.id
					LEFT JOIN marcas AS E ON D.id_marca = E.id
					LEFT JOIN modelos AS F ON D.id_modelo = F.id 
				WHERE A.id=$id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->id_zona = $r->fields['id_zona'];
				$this->numero = $r->fields['numero'];
				$this->fecha = $r->fields['fecha'];
				$this->observaciones = $r->fields['observaciones'];
				$this->estatus = $r->fields['estatus'];
				$this->id_persona = $r->fields['id_persona'];
				$this->letra_per = $r->fields['letra_per'];
				$this->cedula_per = $r->fields['cedula_per'];
				$this->nombre_per = $r->fields['nombre_per'];
				$this->direccion_per = $r->fields['direccion_per'];
				$this->telefono_per = $r->fields['telefono_per'];
				$this->id_experticia = $r->fields['id_experticia'];
				$this->tipo_exp = $r->fields['tipo_exp'];
				$this->costo_exp = $r->fields['costo_exp'];
				$this->id_vehiculo = $r->fields['id_vehiculo'];
				$this->carroceria_veh = $r->fields['carroceria_veh'];
				$this->placa_veh = $r->fields['placa_veh'];
				$this->marca_veh = $r->fields['marca_veh'];
				$this->modelo_veh = $r->fields['modelo_veh'];
				$this->color_veh = $r->fields['color_veh'];
				$this->anio_veh = $r->fields['anio_veh'];
				$this->km = $r->fields['km'];			
				
				$q = "SELECT A.*,B.nombre AS banco FROM pagos AS A LEFT JOIN bancos AS B ON A.id_banco = B.id WHERE id_factura=$id";
				$rCF = $conn->Execute(utf8_encode($q));
				$i=0;
				$this->monto_pagos=0;

				while(!$rCF->EOF){
					$PagosAux[$i][0] = $rCF->fields['tipo_pago'];
					$PagosAux[$i][1] = $rCF->fields['id_banco'];
					$PagosAux[$i][2] = $rCF->fields['banco'];
					$PagosAux[$i][3] = $rCF->fields['referencia'];
					$PagosAux[$i][4] = $rCF->fields['monto'];

					$this->monto_pagos+= $rCF->fields['monto'];

					$i++;
					$rCF->movenext();
				}
				$this->pagos = new Services_JSON();
				$this->pagos = is_array($PagosAux) ? $this->pagos->encode($PagosAux) : false;

				
			}
			 
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function get_all($conn, $orden="A.id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$zona = $_SESSION['SesionZona'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT A.id FROM facturas AS A WHERE id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text AND id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==1){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "WHERE A.numero::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.cedula::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==4){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "INNER JOIN experticias AS B ON A.id_experticia = B.id ";
			$q.= "INNER JOIN tipos_experticia AS C ON B.id_tipo = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==5){
			$q = "SELECT A.id FROM facturas AS A ";
			$q.= "INNER JOIN experticias AS B ON A.id_experticia = B.id ";
			$q.= "INNER JOIN vehiculos AS C ON B.id_vehiculo = C.id ";
			$q.= "WHERE C.placa::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new factura;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function __destruct(){
    }
	
	function add($conn, $id_zona, $fecha, $observaciones, $estatus, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $tipo_exp, $costo_exp, $fecha_exp, $placa_veh, $marca_veh, $modelo_veh, $color_veh, $anio_veh, $pagos, $km, $letra_per, $carroceria_veh){
		try {
 
			//$q = "SELECT (COALESCE(MAX(numero),0) + 1) AS factura FROM facturas WHERE id_zona=$id_zona";
			//$r = $conn->Execute(utf8_encode($q));
			//$numero = $r->fields['factura'];
			
			//Verificar Existencia Persona
			$opersona = new persona;

			$q = "SELECT * FROM personas WHERE letra= '$letra_per' AND cedula = '$cedula_per'";
			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$id_per = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$q = "SELECT * FROM personas WHERE letra='$letra_per' AND cedula = '$cedula_per'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per = $r->fields['id'];
				}

			}
			

			//Verificar Existencia de Vehiculo
			$ovehiculo = new vehiculo;

			$q = "SELECT * FROM vehiculos WHERE carroceria = upper('$carroceria_veh')";
			$r = $conn->Execute(utf8_encode($q));
			
			if (!$r->EOF) {
				$ovehiculo->set($conn, $r->fields['id'], $placa_veh, $marca_veh, $modelo_veh, -1, $color_veh, $anio_veh, -1, $carroceria_veh, '');
				$id_veh = $r->fields['id'];
			} else {
				$r = $ovehiculo->add_returning($conn, $placa_veh, $marca_veh, $modelo_veh, -1, $color_veh, $anio_veh, -1, $carroceria_veh, '');
				//$q = "SELECT MAX(id) AS id FROM vehiculos WHERE carroceria = '$carroceria_veh'";
				//$r = $conn->Execute(utf8_encode($q));
				//if (!$r->EOF) {
				//	$id_veh = $r->fields['id'];
				//}
				$id_veh = $r;
			
			}

			/*
			$otipo_exp = new tipos_experticia;
			$otipo_exp->get($conn,$tipo_exp);
			$pago_exp = guardafloat($otipo_exp->pago);
			*/

			//Crear Experticia
			$q = "INSERT INTO experticias(id_tipo,costo, fecha, id_vehiculo) ";
			$q.= "VALUES($tipo_exp,$costo_exp, '$fecha_exp', $id_veh) ";
			//$q.= "RETURNING id";

			
			$r = $conn->Execute(utf8_encode($q));
			//die($q);
			$this->auditoria($conn, $q);

			$q2 = "SELECT MAX(id) AS id FROM experticias";
			$r2 = $conn->Execute(utf8_encode($q2));
			
			$id_exp = $r2->fields['id']; 
			
			$q = "INSERT INTO facturas ";
			$q.= "( id_zona, id_experticia, id_persona, fecha, estatus, observaciones, km) ";
			$q.= "VALUES ";
			$q.= "($id_zona, $id_exp, $id_per, '$fecha', '$estatus', upper('$observaciones'), $km) ";
			//$q.= "RETURNING id";
			
			//die($q);

			$r = $conn->Execute(utf8_encode($q));

			$this->auditoria($conn, $q);
			
			$q2 = "SELECT MAX(id) AS id FROM facturas";
			$r2 = $conn->Execute(utf8_encode($q2));
			//$this->GuardarPagos($conn, $r->fields['id'],$pagos);
			$this->GuardarPagos($conn, $r2->fields['id'],$pagos);
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set($conn, $id, $id_zona, $fecha, $observaciones, $estatus, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $tipo_exp, $costo_exp, $fecha_exp, $placa_veh, $marca_veh, $modelo_veh, $color_veh, $anio_veh, $pagos, $id_experticia, $numero, $km, $id_vehiculo, $letra_per, $carroceria_veh){
		try {
			//Verificar Existencia Persona
			$opersona = new persona;

			$q = "SELECT * FROM personas WHERE letra = '$letra_per' AND cedula = '$cedula_per'";

			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$id_per = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$q = "SELECT * FROM personas WHERE letra = '$letra_per' AND cedula = '$cedula_per'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per = $r->fields['id'];
				}

			}
			

			//Verificar Existencia de Vehiculo
			$ovehiculo = new vehiculo;

			$q = "SELECT * FROM vehiculos WHERE id = $id_vehiculo";

			$r = $conn->Execute(utf8_encode($q));
			
			if (!$r->EOF) {
				$ovehiculo->set($conn, $r->fields['id'], $placa_veh, $marca_veh, $modelo_veh, -1, $color_veh, $anio_veh, -1, $carroceria_veh, '');
				$id_veh = $r->fields['id'];
			} else {
				$r = $ovehiculo->add_returning($conn, $placa_veh, $marca_veh, $modelo_veh, -1, $color_veh, $anio_veh, -1, $carroceria_veh, '');
				//$q = "SELECT MAX(id) AS id FROM vehiculos WHERE carroceria = '$carroceria_veh'";
				//$r = $conn->Execute(utf8_encode($q));
				//if (!$r->EOF) {
				//	$id_veh = $r->fields['id'];
				//}
				$id_veh = $r;

			}

			/*
			$otipo_exp = new tipos_experticia;
			$otipo_exp->get($conn,$tipo_exp);
			$pago_exp = guardafloat($otipo_exp->pago);
			*/

			//UPDATE Experticia
			$q = "UPDATE experticias SET id_tipo = $tipo_exp, costo = $costo_exp, fecha = '$fecha_exp', id_vehiculo = $id_veh ";
			$q.= "WHERE id = $id_experticia";

			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$q = "UPDATE facturas ";
			$q.= "SET id_zona = $id_zona, id_experticia=$id_experticia, id_persona=$id_per, fecha='$fecha', estatus='$estatus', observaciones=upper('$observaciones'), km = $km ";
			$q.= "WHERE id = $id";		
			//die($q);
	
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$this->GuardarPagos($conn, $id,$pagos);

			return REG_SET_OK;

		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM facturas WHERE id=$id";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarPagos($conn,$id,$pagos){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$pagos));
			$q = "DELETE FROM pagos WHERE id_factura=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->pagos)){
				foreach($JsonRec->pagos as $pagosAux){
					$monto = guardafloat($pagosAux[4]);
					$q = "INSERT INTO pagos ";
					$q.= "(id_factura, tipo_pago, id_banco,  referencia, monto) ";
					$q.= "VALUES ";
					$q.= "($id, $pagosAux[0], NULLIF('$pagosAux[1]','')::integer, upper('$pagosAux[3]'), $monto) ";
					
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}


	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="A.id")
	{
		$zona = $_SESSION['SesionZona'];
		$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM facturas AS A WHERE id_zona = $zona ";
			}
			elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text AND id_zona = $zona ";
			}
			elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "WHERE A.numero::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.cedula::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==4){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "INNER JOIN experticias AS B ON A.id_experticia = B.id ";
			$q.= "INNER JOIN tipos_experticia AS C ON B.id_tipo = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==5){
			$q = "SELECT count(*) AS total FROM facturas AS A ";
			$q.= "INNER JOIN experticias AS B ON A.id_experticia = B.id ";
			$q.= "INNER JOIN vehiculos AS C ON B.id_vehiculo = C.id ";
			$q.= "WHERE C.placa::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}	

	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}		
	
}
?>
